package fr.miam.android_playboy;
import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SqlConnect extends SQLiteOpenHelper {

	static protected final String SQL_NAME = "pictures.db" ;
	static protected final int SQL_VERSION = 1 ;
	
	protected SQLiteDatabase db = null ;

	
	public SqlConnect(Context context) {
		super(context, SQL_NAME, null, SQL_VERSION);
		db = getWritableDatabase();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE people " +
			"(id INT(11) NOT NULL, uri VARCHAR(255))");
		db.execSQL("CREATE TABLE picture " +
				"(id INT(11) NOT NULL, uri VARCHAR(255))");
		db.execSQL("CREATE TABLE annotations " +
				"(people_id INT(11) NOT NULL, picture_id INT(11) NOT NULL)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
	}
	
	public void insertQuery (String query) {
		this.db.execSQL(query);
		this.db.close();
	}
	
	public Cursor select (String requete) {
		Cursor cursor = this.db.rawQuery(requete, null);
	    return cursor;
	}
	
	public void close() {
		this.db.close();
	}
	
	public boolean insertAnnotation (String people_uri, String picture_uri) {
		//Insertion du contect dans la base
		this.db.execSQL("INSERT INTO people VALUES ("+
				"(SELECT COUNT(*)+1 FROM people), '"+people_uri+"')");
		Cursor select_people = this.select("SELECT MAX(id) AS id FROM people WHERE uri = '"+people_uri+"'");
		int people_id = 0;
		while (select_people.moveToNext()) {
			people_id = select_people.getInt(select_people.getColumnIndex("id"));
		}
		
		if (people_id > 0) {
			Cursor picture_exist = this.select ("SELECT COUNT(*) AS count FROM picture WHERE uri = '"+picture_uri+"'");
			picture_exist.moveToNext();
			
			if (picture_exist.getInt(picture_exist.getColumnIndex("count")) == 0) {
				this.db.execSQL("INSERT INTO picture VALUES ("+
					"(SELECT COUNT(*)+1 FROM picture), '"+picture_uri+"')");
			}
			
			Cursor select_picture = this.select("SELECT MAX(id) AS id FROM picture WHERE uri = '"+picture_uri+"'");
			int picture_id = 0;
			while (select_picture.moveToNext()) {
				picture_id = select_picture.getInt(select_picture.getColumnIndex("id"));
			}
			
			if (picture_id > 0) {
				this.db.execSQL("INSERT INTO annotations VALUES ("+people_id+", "+picture_id+")");
				
				return true;
			}
		}
		
		return false;
	}
	
	public Cursor getPeoplesForPicture(String picture_uri) {
		String query = "SELECT p.uri AS people_uri FROM annotations AS a " +
				"LEFT JOIN people AS p ON p.id = a.people_id " +
				"LEFT JOIN picture AS pi ON pi.id = a.picture_id " +
				"WHERE pi.uri = '"+picture_uri+"'";
		
		Cursor cursor = this.select(query);
		
		return cursor;
	}
	
	public boolean deletePeople(String people_uri, String picture_uri) {
		Cursor select_people = this.select("SELECT id FROM people WHERE uri = '"+people_uri+"'");
		select_people.moveToNext();
		int people_id = select_people.getInt(select_people.getColumnIndex("id"));
		if (people_id > 0) {
			this.db.execSQL("DELETE FROM people WHERE uri = '"+people_uri+"'");
			
			Cursor select_picture = this.select("SELECT id FROM picture WHERE uri = '"+picture_uri+"'");
			select_picture.moveToNext();
			int picture_id = select_picture.getInt(select_picture.getColumnIndex("id"));
			if (picture_id > 0) {
				this.db.execSQL("DELETE FROM annotations WHERE people_id = "+people_id+" AND picture_id = "+picture_id);
				
				Cursor picture_exist = this.select("SELECT COUNT(*) AS count FROM annotations WHERE picture_id = "+picture_id);
				picture_exist.moveToNext();
				boolean picture = picture_exist.getInt(picture_exist.getColumnIndex("count")) > 0;
				if (!picture) {
					this.db.execSQL("DELETE FROM picture WHERE id = "+picture_id);
				}
				
				return true;
			}
		}
		
		return false;
	}
}
